import threading

import pymysql
import traceback

# db = []
class TM_DbOperate:
    # def __init__(self):
        # 打开数据库连接
        # self.db = db
        # 使用cursor()方法获取操作游标
        # self.cursor = db.cursor()
        # 使用预处理语句创建表
        # self.initTables()  #初始化数据表
    def execBySqlVal(self,sql,value):
        print("sql________:",sql)
        print("value++++++++:",value)
        try:
            db = pymysql.connect("localhost", "root", "123456", "dolin", charset='utf8mb4' )
            # db.ping(reconnect=True)
            cursor = db.cursor()
            # 执行sql语句
            lock = threading.Lock()
            lock.acquire()
            cursor.execute(sql,value)
            insertId = db.insert_id()
            # 提交到数据库执行
            db.commit()
            lock.release()
            return insertId
        except Exception as e:
            # 如果发生错误则回滚
            print('execBySqlVal Exception :', e)
            traceback.print_exc()
            return 0
            # db.rollback()
        # 使用 fetchone() 方法获取一条数据
        # data = cursor.fetchone()
        # print ("Database version : %s " % data)
    def execBySqlVals(self,sql,values):
        # print("sql:",sql)
        # print("values:",values)
        try:
            db = pymysql.connect("localhost", "root", "123456", "dolin", charset='utf8mb4' )
            # db.ping(reconnect=True)
            cursor = db.cursor()
            # 执行sql语句
            lock = threading.Lock()
            lock.acquire()
            cursor.executemany(sql,values)
            # 提交到数据库执行
            db.commit()
            lock.release()
            return 1
        except Exception as e:
            # 如果发生错误则回滚
            print('execBySqlVals Exception :', e)
            traceback.print_exc()
            return 0
            # db.rollback()
    def execBySql(self,sql):
        # print("sql:",sql)
        try:
            db = pymysql.connect("localhost", "root", "123456", "dolin", charset='utf8mb4' )
            # db.ping(reconnect=True)
            cursor = db.cursor()
            lock = threading.Lock()
            lock.acquire()
            cursor.execute(sql)
            db.commit()
            lock.release()
            return cursor.fetchall()
        except Exception as e:
            print('execBySql Exception :', e)
            traceback.print_exc()
            db.rollback()
    # def __exit__(self, exc_type, exc_val, exc_tb):
        # 关闭数据库连接
        # db.close()
    def initTables(self):
        #初始化数据表
        #`hasdone` decimal(50,0) DEFAULT '0' COMMENT '本月成交数量',
        self.execBySql("DROP TABLE IF EXISTS tb_g_goods")
        sql = """CREATE TABLE `tb_g_goods` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT,
              `name` varchar(255) DEFAULT '无' COMMENT '商品名称',
              `title` varchar(255) DEFAULT '无' COMMENT '商品标题',
              `brandname` varchar(50) DEFAULT '无' COMMENT '品牌名称',
              `model` varchar(50) DEFAULT '无' COMMENT '型号',
              `shopname` varchar(100) DEFAULT '无' COMMENT '店铺名称',
              `productId` bigint(20) DEFAULT NULL,
              `price` varchar(50) DEFAULT '0.00' COMMENT '价格',
              `oldprice` varchar(50) DEFAULT '0.00' COMMENT '价格',
              PRIMARY KEY (`id`),
              KEY `id` (`id`) USING BTREE,
              KEY `shopname` (`shopname`),
              KEY `title` (`title`)
            ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;"""
        self.execBySql(sql)
        #####################################
        self.execBySql("DROP TABLE IF EXISTS tb_a_assess")
        sql = """CREATE TABLE `tb_a_assess` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT,
              `goodsTableId` bigint(20) NOT NULL,
              `headurl` varchar(255) DEFAULT '无' COMMENT '用户头像地址',
              `username` varchar(255) DEFAULT '无' COMMENT '用户名称',
              `level` int(11) DEFAULT '0' COMMENT '评论等级 1=差评 2=较差 3=中评 4=较好评 5=好评',
              `content` text COMMENT '评价内容',
              `assesstime` varchar(255) DEFAULT '无' COMMENT '评价时间',
              `productId` bigint(20) DEFAULT NULL,
              PRIMARY KEY (`id`),
              KEY `id` (`id`),
              KEY `gid` (`goodsTableId`),
              KEY `headurl` (`headurl`),
              KEY `username` (`username`)
            ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4;"""
        self.execBySql(sql)

# d = DbOperate().execBySql("SELECT VERSION()")
# print(d)